package birs.controller.expanditure.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.expanditure.XPD002Model;
import birs.model.expanditure.XPD002ModelA;
import birs.model.expanditure.XPD002ModelB;
import birs.model.expanditure.XPD002ModelC;
import birs.model.expanditure.XPD002ModelD;
import birs.model.expanditure.XPD002ModelE;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class XPD002Task {
	public void getExpanditureAllIncome(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException{
		List<XPD002Model> xpd002ModelList = getExpanditureByYear();
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelList);
		sendResponse(request, response, resultJson);
	}
	
	public void getFirstDrilldown(HttpServletRequest request, HttpServletResponse response) throws IOException{
		List<XPD002ModelA> xpd002ModelA = getExpanditureByBudgetGroup(request.getParameter("period"));
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelA);
		sendResponse(request, response, resultJson);
	}
	
	public void getSecondDrilldown(HttpServletRequest request, HttpServletResponse response) throws IOException{
		List<XPD002ModelB> xpd002ModelBs = getExpanditureByDepartment(request.getParameter("period"),request.getParameter("budgetId"));
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelBs);
		sendResponse(request, response, resultJson);
	}
	
	public void getThirdDrilldown(HttpServletRequest request, HttpServletResponse response) throws IOException{
		List<XPD002ModelC> xpd002ModelCs = getExpanditureByProject(request.getParameter("period"), request.getParameter("budgetId"), request.getParameter("depId"));
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelCs);
		sendResponse(request, response, resultJson);
	}
	
	public void getTreeGraph(HttpServletRequest request, HttpServletResponse response) throws IOException{
		List<XPD002Model> xpd002ModelList=null;
		xpd002ModelList = getExpanditureByYear();
		
		//cululate amount for every year
		double sum = 0.0;
		//get expanditure by group
		for (XPD002Model xpd002Model : xpd002ModelList) {
			List<XPD002ModelA> xpd002ModelAList = getExpanditureByBudgetGroup2(String.valueOf(xpd002Model.getName()));
			if(xpd002ModelAList.size() > 0){
				xpd002Model.setChildren(xpd002ModelAList);
				//xpd002Model.setAmount(null);
			}
			//get expanditure by department
			for (XPD002ModelA xpd002ModelA : xpd002ModelAList) {
				List<XPD002ModelB> xpd002ModelBList = getExpanditureByDepartment(String.valueOf(xpd002Model.getName()),String.valueOf(xpd002ModelA.getBudgetGroupId()));
				if(xpd002ModelBList.size() > 0){
					xpd002ModelA.setChildren(xpd002ModelBList);
					//xpd002ModelA.setAmount(null);
				}
				//get expanditure by project
				for (XPD002ModelB xpd002ModelB : xpd002ModelBList) {
					List<XPD002ModelC> xpd002ModelCList = getExpanditureByProject(String.valueOf(xpd002Model.getName()), String.valueOf(xpd002ModelA.getBudgetGroupId()), String.valueOf(xpd002ModelB.getDepId()));
					if(xpd002ModelCList.size()>0){
						xpd002ModelB.setChildren(xpd002ModelCList);
						//xpd002ModelB.setAmount(null);
					}
					
					// filter amount < 0 
//					List<XPD002ModelC> xpd002ModelCList2 = new ArrayList<XPD002ModelC>();
//					for (XPD002ModelC xpd002ModelC : xpd002ModelCList) {
//						if(xpd002ModelC.getAmount() > 5000){
//							xpd002ModelCList2.add(xpd002ModelC);
//						}
//					}
//					xpd002ModelB.setChildren(xpd002ModelCList2);
				}
			}
			sum += xpd002Model.getAmount();
		}

		XPD002ModelD xpd002ModelD = new XPD002ModelD();
		xpd002ModelD.setChildren(xpd002ModelList);
		xpd002ModelD.setName("งบประมาณ");
		xpd002ModelD.setAmount(sum);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelD);
		sendResponse(request, response, resultJson);
	}
	
	public void getRadioBox(HttpServletRequest request, HttpServletResponse response){
		List<XPD002ModelE> xpd002ModelEList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD002ModelE> xpd001ModelE = new ResultSetMapper<XPD002ModelE>();
			xpd002ModelEList = xpd001ModelE.mapRersultSetToObject(getRadioBoxSQL(con), XPD002ModelE.class);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelEList);
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getSunburstGraph(HttpServletRequest request, HttpServletResponse response) throws IOException{
		//get expanditure by group
		List<XPD002ModelA> xpd002ModelAList = getExpanditureByBudgetGroup(request.getParameter("year"));
		
		//get expanditure by department
		for (XPD002ModelA xpd002ModelA : xpd002ModelAList) {
			List<XPD002ModelB> xpd002ModelBList = getExpanditureByDepartment(request.getParameter("year"),String.valueOf(xpd002ModelA.getBudgetGroupId()));
			if(xpd002ModelBList.size() > 0){
				xpd002ModelA.setChildren(xpd002ModelBList);
			}
			//get expanditure by project
			for (XPD002ModelB xpd002ModelB : xpd002ModelBList) {
				List<XPD002ModelC> xpd002ModelCList = getExpanditureByProject(request.getParameter("year"), String.valueOf(xpd002ModelA.getBudgetGroupId()), String.valueOf(xpd002ModelB.getDepId()));
				if(xpd002ModelCList.size()>0){
					xpd002ModelB.setChildren(xpd002ModelCList);
					//xpd002ModelB.setAmount(null);
				}
				//List for remove object
//				List<XPD002ModelC> xpd002ModelForRemove = new ArrayList<XPD002ModelC>();
//				for (XPD002ModelC xpd002ModelC : xpd002ModelCList) {
//					if(xpd002ModelC.getAmount() < 5000){
//						xpd002ModelForRemove.add(xpd002ModelC);
//					}
//				}
//				xpd002ModelCList.removeAll(xpd002ModelForRemove);
			}
		}
		XPD002ModelD xpd002ModelD = new XPD002ModelD();
		xpd002ModelD.setName(request.getParameter("year"));
		xpd002ModelD.setChildren(xpd002ModelAList);
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd002ModelD);
		sendResponse(request, response, resultJson);
	}
	
	private List<XPD002Model> getExpanditureByYear(){
		List<XPD002Model> xpd002ModelList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD002Model> xpd002Model = new ResultSetMapper<XPD002Model>();
			xpd002ModelList = xpd002Model.mapRersultSetToObject(getExpanditureByYearSQL(con), XPD002Model.class);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return xpd002ModelList;
	}
	
	private List<XPD002ModelA> getExpanditureByBudgetGroup(String period){
		List<XPD002ModelA> xpd002ModelAList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD002ModelA> xpd002ModelA = new ResultSetMapper<XPD002ModelA>();
			xpd002ModelAList = xpd002ModelA.mapRersultSetToObject(getExpanditureByBudgetGroupSQL(period,con), XPD002ModelA.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd002ModelAList;
	}
	
	private List<XPD002ModelA> getExpanditureByBudgetGroup2(String period){
		List<XPD002ModelA> xpd002ModelAList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD002ModelA> xpd002ModelA = new ResultSetMapper<XPD002ModelA>();
			xpd002ModelAList = xpd002ModelA.mapRersultSetToObject(getExpanditureByBudgetGroupSQL2(period,con), XPD002ModelA.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd002ModelAList;
	}
	
	private List<XPD002ModelB> getExpanditureByDepartment(String period,String budgetId){
		List<XPD002ModelB> xpd002ModelBList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD002ModelB> xpd002ModelB = new ResultSetMapper<XPD002ModelB>();
			xpd002ModelBList = xpd002ModelB.mapRersultSetToObject(getExpanditureByDepartmentSQL(period,budgetId,con), XPD002ModelB.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd002ModelBList;
	}
	
	private List<XPD002ModelC> getExpanditureByProject(String period, String budgetId, String depId) {
		List<XPD002ModelC> xpd002ModelCList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD002ModelC> xpd002ModelC = new ResultSetMapper<XPD002ModelC>();
			xpd002ModelCList = xpd002ModelC.mapRersultSetToObject(getExpanditureByProjectSQL(period, budgetId, depId, con), XPD002ModelC.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd002ModelCList;
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}

	private ResultSet getExpanditureByYearSQL(Connection con) throws SQLException{
		String sql = " 	SELECT D.BUDGETPERIODID,SUM(CURRENTBUDGET*Atype)+	"
				+" 	  SUM(CURRENTBUDGET     *Btype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ctype)+	"
				+" 	  SUM(CURRENTBUDGET     *Dtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Etype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ftype)+	"
				+" 	  SUM(CURRENTBUDGET     *Gtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Htype)+	"
				+" 	  SUM(CURRENTBUDGET     *Itype)+	"
				+" 	  SUM(CURRENTBUDGET     *Jtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ktype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ltype)+	"
				+" 	  SUM(CURRENTBUDGET     *Mtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ntype)+	"
				+" 	  SUM(CURRENTBUDGET     *Otype) AS AMOUNT	"
				+" 	FROM PROMIS.v_bud_linktype D	"
				+" 	WHERE D.BUDGETPERIODID <= (SELECT TO_CHAR(SYSDATE, 'yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') AS YEAR FROM DUAL)	"
				+" 	GROUP BY D.BUDGETPERIODID	"
				+" 	ORDER BY D.BUDGETPERIODID";
		return getData(sql,con);
	}
	
	private ResultSet getExpanditureByBudgetGroupSQL(String period,Connection con) throws SQLException{
		String sql = " 	SELECT D.BUDGETPERIODID,D.BUDGETGROUPID, B.BUDGETGROUPNAME, SUM(CURRENTBUDGET*Atype)+	"
				+" 	  SUM(CURRENTBUDGET     *Btype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ctype)+	"
				+" 	  SUM(CURRENTBUDGET     *Dtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Etype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ftype)+	"
				+" 	  SUM(CURRENTBUDGET     *Gtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Htype)+	"
				+" 	  SUM(CURRENTBUDGET     *Itype)+	"
				+" 	  SUM(CURRENTBUDGET     *Jtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ktype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ltype)+	"
				+" 	  SUM(CURRENTBUDGET     *Mtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ntype)+	"
				+" 	  SUM(CURRENTBUDGET     *Otype) AS AMOUNT	"
				+" 	FROM PROMIS.v_bud_linktype D , BUDGETGROUP B	"
				+" 	WHERE D.BUDGETPERIODID = "+period
				+" 	AND D.BUDGETGROUPID = B.BUDGETGROUPID	"
				+" 	GROUP BY D.BUDGETPERIODID,D.BUDGETPERIODID,D.BUDGETGROUPID,B.BUDGETGROUPNAME	"
				+" 	ORDER BY AMOUNT ";
		return getData(sql,con);
	}
	
	private ResultSet getExpanditureByBudgetGroupSQL2(String period,Connection con) throws SQLException{
		String sql = " 	SELECT D.BUDGETPERIODID,D.BUDGETGROUPID, B.BUDGETGROUPNAME, SUM(CURRENTBUDGET*Atype)+	"
				+" 	  SUM(CURRENTBUDGET     *Btype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ctype)+	"
				+" 	  SUM(CURRENTBUDGET     *Dtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Etype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ftype)+	"
				+" 	  SUM(CURRENTBUDGET     *Gtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Htype)+	"
				+" 	  SUM(CURRENTBUDGET     *Itype)+	"
				+" 	  SUM(CURRENTBUDGET     *Jtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ktype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ltype)+	"
				+" 	  SUM(CURRENTBUDGET     *Mtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ntype)+	"
				+" 	  SUM(CURRENTBUDGET     *Otype) AS AMOUNT	"
				+" 	FROM PROMIS.v_bud_linktype D , BUDGETGROUP B	"
				+" 	WHERE D.BUDGETPERIODID = "+period
				+" 	AND D.BUDGETGROUPID = B.BUDGETGROUPID	"
				+" 	GROUP BY D.BUDGETPERIODID,D.BUDGETPERIODID,D.BUDGETGROUPID,B.BUDGETGROUPNAME	"
				+" 	ORDER BY AMOUNT DESC";
		return getData(sql,con);
	}
	
	private ResultSet getExpanditureByDepartmentSQL(String period,String budgetId,Connection con) throws SQLException{
		String sql = " 	SELECT D.DEPARTMENTID,D.DEPARTMENTNAME,SUM(CURRENTBUDGET*Atype)+	"
				+" 	  SUM(CURRENTBUDGET     *Btype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ctype)+	"
				+" 	  SUM(CURRENTBUDGET     *Dtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Etype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ftype)+	"
				+" 	  SUM(CURRENTBUDGET     *Gtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Htype)+	"
				+" 	  SUM(CURRENTBUDGET     *Itype)+	"
				+" 	  SUM(CURRENTBUDGET     *Jtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ktype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ltype)+	"
				+" 	  SUM(CURRENTBUDGET     *Mtype)+	"
				+" 	  SUM(CURRENTBUDGET     *Ntype)+	"
				+" 	  SUM(CURRENTBUDGET     *Otype) AS AMOUNT	"
				+" 	FROM PROMIS.v_bud_linktype D , BUDGETGROUP B	"
				+" 	WHERE D.BUDGETPERIODID = "+period
				+" 	AND D.BUDGETGROUPID = "+budgetId
				+" 	AND B.BUDGETGROUPID = "+budgetId
				+" 	GROUP BY D.DEPARTMENTID,D.DEPARTMENTNAME	"
				+" 	ORDER BY AMOUNT DESC";
		return getData(sql,con);
	}
	
	private ResultSet getExpanditureByProjectSQL(String period,String budgetId,String depId,Connection con) throws SQLException{
		String sql = " 	SELECT BN.BUDGETNAME,SUM(BN.CURRENTBUDGET) AS AMOUNT	"
				+" 	FROM PROMIS.V_BUD_BUDGETINFO BN  	"
				+" 	WHERE  BN.BUDGETPERIODID = "+period
				+" 	  and BN.BUDGETGROUPID = "+budgetId
				+" 	  AND BN.DEPARTMENTID= "+depId
				+" 	GROUP BY BN.BUDGETNAME	"
				+" 	ORDER BY AMOUNT DESC";
		 
		return getData(sql,con);
	}
	
	private ResultSet getRadioBoxSQL(Connection con) throws SQLException{
		String sql = " 	SELECT D.BUDGETPERIODID "
				+" 	FROM PROMIS.v_bud_linktype D	"
				+" 	WHERE D.BUDGETPERIODID <= (SELECT TO_CHAR(SYSDATE, 'yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') AS YEAR FROM DUAL)	"
				+" 	GROUP BY D.BUDGETPERIODID	"
				+" 	ORDER BY D.BUDGETPERIODID DESC";
		return getData(sql,con);
	}
	
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}